<?php
/****************************************************************************************************** 
	This class is part of the database abstraction layer.  This deals with all user specific queries.
	Provides much more security.  Uses mySQL built in aes_encryption.
	addslashes() used on all usernames to prevent sql injections.
******************************************************************************************************/

require_once(ROOTDIR."/include/objects/class.User.php"); // Used to store user info
require_once(ROOTDIR."/include/objects/class.BannedUser.php"); // Used to store banned user info
require_once(ROOTDIR."/include/database/class.Database.php"); //Database class
require_once(ROOTDIR."/config/class.Config.php");

class UserQueries extends Config {	

	// Variables
	var $db; // Object of database class
	var $user; // Object to store user info
	
	// Default constructor
	function __construct() {
		$this->init();
  }
  
	function userQueries() {
		$this->init();
  }
  
  function init() {
    $this->db = new Database; // Create new database connection
		$this->user = new User; // Create a User object
  }
		
	/************************************************************************
	Check for username existance in database 
	Return ture if the username is in the database
	************************************************************************/
	function usernameExist($name) {	
		
		// Query the database to see if the specified email exists
		$this->db->sql_query("SELECT admin_id FROM gban_admins WHERE name='".addslashes($name)."'");
		
		// It does exist
		if($this->db->get_row()) {
			return true;
		}
		
		return false;
	}
	
	/************************************************************************
	Determine if the steam id passed in belongs to a member
	************************************************************************/
	function isMember($steamId) {
    $steamId = trim($steamId);
    
    // Query the database to see if the specified email exists
		$this->db->sql_query("SELECT admin_id FROM gban_admins WHERE steam_id='".addslashes($steamId)."'");

		// It does exist
		if($this->db->get_row()) {
			return true;
		}

		return false;
  }
	
	/************************************************************************
	Check for email existance in database 
	Return true if the email is in the database
	************************************************************************/
	function emailExist($email) {	
		
		// Query the database to see if the specified email exists
		$this->db->sql_query("SELECT admin_id FROM gban_admins WHERE email='".$email."'");
		
		// It does exist
		if($this->db->get_row()) {
			return true;
		}
		
		return false;
	}
	
	/************************************************************************
	Add new user to the database 
	************************************************************************/
	function addUser($name, $password, $accessLevel, $steamId, $email) {
		
		// Check if the fields have values before inserting
		if(!empty($name) || !empty($password) || !empty($steamId)) {		
			// Encrypt password
			$this->db->sql_query("INSERT INTO gban_admins (name, password, email, access_level) 
			values ('".addslashes($name)."', 
			'".md5($password)."', 
			'".$email."',
			'".$accessLevel."')");
			
			$banId = $this->db->get_insert_id();
			
			// Add their steam id to the database
			$this->db->sql_query("INSERT INTO gban_admin_steam (admin_id, steam_id)
      values('".$banId."', '".$steamId."')");
												
			// Make sure query executed succesfully
			if($this->db->sql_result) {	
				return true;
			}
			else {
				return false;
			}
		}
		return false;
	}
	
 /************************************************************************
	Add new smf user to the gban admin table
	************************************************************************/
	function addSmfUser($id, $steamId) {

		// Check if the fields have values before inserting
		if(!empty($steamId)) {
			// Add their steam id to the database
			$this->db->sql_query("INSERT INTO gban_admin_steam (admin_id, steam_id)
      values('".$id."', '".$steamId."')");

			// Make sure query executed succesfully
			if($this->db->sql_result) {
				return true;
			}
			else {
				return false;
			}
		}
		return false;
	}
	
	/************************************************************************
	Verify that the username and password match those in the database
	************************************************************************/
	function verifyUser($name, $password) {
	   	
		$this->db->sql_query("SELECT admin_id 
                          FROM gban_admins 
		                      WHERE name='".addslashes($name)."' AND password='".md5($password)."'");
				
		// Determine if verification passed or not
		if($this->db->get_row()) {
			return true;
		}
		else {
			return false;
		}
	}
	
	/************************************************************************
	Get everything about the user and store it to a user object
	************************************************************************/
	function getUserInfo($username) {

    if($this->enableSmfIntegration) {
      $this->db->sql_query("SELECT gas.admin_id, sm.memberName, sm.ID_GROUP, sm.additonalGroups, sm.emailAddress
                            FROM gban_admin_steam gas, smf_members sm
                            WHERE sm.memberName = '".$username."'
                            AND sm.ID_MEMBER = gas.admin_id");

      $info = $this->db->get_row();

      if($info) {
  			// Set variables in user object
  			$this->user->setId($info['admin_id']);
  			$this->user->setName($info['memberName']);
  			$this->user->setAccessLevel($this->getAccessLevel($info['ID_GROUP'], $info['additionalGroups']));
  			$this->user->setEmail($info['emailAddress']);

  			return $this->user; // Return user object
  		}
    } else {
  		$this->db->sql_query("SELECT ga.admin_id, ga.name, ga.password, ga.access_level, ga.email, gas.steam_id
  		                      FROM gban_admins ga, gban_admin_steam gas
                            WHERE name='".addslashes($username)."'
                            AND ga.admin_id = gas.admin_id");

  		$info = $this->db->get_row();

  		if($info) {
  			// Set variables in user object
  			$this->user->setId($info['admin_id']);
  			$this->user->setName($info['name']);
  			$this->user->setPassword($info['password']);
  			$this->user->setAccessLevel($info['access_level']);
  			$this->user->setEmail($info['email']);
  			$this->user->setSteamId($info['steam_id']);

  			return $this->user; // Return user object
  		}
  		else {
  			return new User();
  		}
		}
		return new User(); // We should never get to here
	}
	
	/************************************************************************
	Get everything about the user and store it to a user object
	************************************************************************/
	function getUserInfoById($id) {
    if($this->enableSmfIntegration) {
      $this->db->sql_query("SELECT gas.admin_id, sm.memberName, sm.ID_GROUP, sm.additionalGroups, sm.emailAddress
                            FROM gban_admin_steam gas, smf_members sm
                            WHERE gas.admin_id = '".$id."'
                            AND sm.ID_MEMBER = gas.admin_id");

  		$info = $this->db->get_row();

  		if($info) {
  			// Set variables in user object
  			$this->user->setId($info['admin_id']);
  			$this->user->setName($info['memberName']);
  			$this->user->setAccessLevel($this->getAccessLevel($info['ID_GROUP'], $info['additionalGroups']));
  			$this->user->setEmail($info['emailAddress']);

  			return $this->user; // Return user object
  		}
  		else {
  			return new User();
  		}
    } else {
  		$this->db->sql_query("SELECT ga.admin_id, ga.name, ga.password, ga.access_level, ga.email, gas.steam_id
  		                      FROM gban_admins ga, gban_admin_steam gas
                            WHERE ga.admin_id='".$id."'
                            AND ga.admin_id = gas.admin_id");

  		$info = $this->db->get_row();

  		if($info) {
  			// Set variables in user object
  			$this->user->setId($info['admin_id']);
  			$this->user->setName($info['name']);
  			$this->user->setPassword($info['password']);
  			$this->user->setAccessLevel($info['access_level']);
  			$this->user->setEmail($info['email']);

  			return $this->user; // Return user object
  		}
  		else {
  			return new User();
  		}
		}
	}
	
	/************************************************************************
	Get everything about the user and store it to a user object
	************************************************************************/
	function getUserInfoBySteamId($steamId) {
	
    if($this->enableSmfIntegration) {
  		$this->db->sql_query("SELECT gas.admin_id, sm.memberName, sm.ID_GROUP, sm.additionalGroups, sm.emailAddress
                            FROM gban_admin_steam gas, smf_members sm
                            WHERE gas.steam_id = '".$steamId."'
                            AND sm.ID_MEMBER = gas.admin_id");
                            
      $info = $this->db->get_row();
      
      if($info) {
  			// Set variables in user object
  			$this->user->setId($info['admin_id']);
  			$this->user->setName($info['memberName']);
  			$this->user->setAccessLevel($this->getAccessLevel($info['ID_GROUP'], $info['additionalGroups']));
  			$this->user->setEmail($info['emailAddress']);

  			return $this->user; // Return user object
  		}
  		else {
  			return new User();
  		}
    } else {
      $this->db->sql_query("SELECT ga.admin_id, ga.name, ga.password, ga.access_level, ga.email, gas.steam_id
  		                      FROM gban_admins ga, gban_admin_steam gas
                            WHERE gas.steam_id ='".$steamId."'
                            AND ga.admin_id = gas.admin_id");
                            
      $info = $this->db->get_row();

  		if($info) {
  			// Set variables in user object
  			$this->user->setId($info['admin_id']);
  			$this->user->setName($info['name']);
  			$this->user->setPassword($info['password']);
  			$this->user->setAccessLevel($info['access_level']);
  			$this->user->setEmail($info['email']);

  			return $this->user; // Return user object
  		}
  		else {
  			return new User();
  		}
    }
		return new User(); // We should never get to here
	}
	
	/************************************************************************
	This method updates the info in the database with the info found in 
	the $user object.
	************************************************************************/
	function updateUser($user) {
	  // Update the gban_admins table if SMF integration is off
    if(!$this->enableSmfIntegration) {
  		$this->db->sql_query("UPDATE gban_admins SET
  			name='".$user->getName()."',
  			email='".$user->getEmail()."',
  			access_level='".$user->getAccessLevel()."',
  			password='".$user->getPassword()."'
  			WHERE admin_id='".$user->getId()."'");
		}
		// Update their steam id
	  $this->db->sql_query("UPDATE gban_admin_steam SET 
                          steam_id = '".$user->getSteamId()."'
                          WHERE admin_id = '".$user->getId()."'");
	}
	
	/************************************************************************
	Get the list of users in the database
	************************************************************************/
	function getUsers() {
	
    if($this->enableSmfIntegration) {
      $this->db->sql_query("SELECT sm.ID_MEMBER, sm.memberName, sm.ID_GROUP, sm.additionalGroups, sm.emailAddress, gas.steam_id
                            FROM smf_members sm, gban_admin_steam gas
                            WHERE sm.ID_MEMBER = gas.admin_id
                            ORDER BY sm.ID_GROUP, sm.memberName ASC");

  		$users = $this->db->get_array();

  		$userList = array(); // Array of user objects

  		for($i=0; $i<count($users); $i++) {
        $user = new User();

        $user->setId($users[$i]['ID_MEMBER']);
  			$user->setName(stripslashes($users[$i]['memberName']));
  			$user->setAccessLevel($this->getAccessLevel($users[$i]['ID_GROUP'], $users[$i]['additionalGroups']));
  			$user->setEmail($users[$i]['emailAddress']);
  			$user->setSteamId($users[$i]['steam_id']);

        // Do not add those that fail
        if($user->getAccessLevel() > 0) {
          array_push($userList, $user); // Add the user object to the array
        }
      }
      
      usort($userList, array("User", "cmp_obj"));
      
      return $userList;
    } else {
      $this->db->sql_query("SELECT ga.admin_id, ga.name, ga.access_level, ga.email, gas.steam_id
                            FROM gban_admins ga, gban_admin_steam gas
                            WHERE ga.admin_id = gas.admin_id
                            ORDER BY ga.access_level, ga.name ASC");

  		$users = $this->db->get_array();

  		$userList = array(); // Array of user objects

  		for($i=0; $i<count($users); $i++) {
        $user = new User();

        $user->setId($users[$i]['admin_id']);
  			$user->setName(stripslashes($users[$i]['name']));
  			$user->setAccessLevel($users[$i]['access_level']);
  			$user->setEmail($users[$i]['email']);
  			$user->setSteamId($users[$i]['steam_id']);

        array_push($userList, $user); // Add the user object to the array
      }

      return $userList;
    }
    return array();
  }
  
  /************************************************************************
	Get a list of SMF users that are in 1 of the 4 groups
	************************************************************************/
  function getSMFUsers() {
    $this->db->sql_query("SELECT sm.ID_MEMBER, sm.memberName, sm.ID_GROUP, sm.additionalGroups, sm.emailAddress
                          FROM smf_members sm
                          WHERE sm.ID_MEMBER NOT IN (
                            SELECT gas.admin_id
                            FROM gban_admin_steam gas
                            )
                          AND sm.ID_GROUP IN (".$this->memberGroup.", ".$this->adminGroup.", ".$this->banManagerGroup.", ".$this->fullPowerGroup.")");

		$users = $this->db->get_array();

		$userList = array(); // Array of user objects

		for($i=0; $i<count($users); $i++) {
      $user = new User();

      $user->setId($users[$i]['ID_MEMBER']);
			$user->setName(stripslashes($users[$i]['memberName']));
			$user->setAccessLevel($this->getAccessLevel($users[$i]['ID_GROUP'], $users[$i]['additionalGroups']));
			$user->setEmail($users[$i]['emailAddress']);
			$user->setSteamId($users[$i]['steam_id']);

      // Do not add those that fail
      if($user->getAccessLevel() > 0) {
        array_push($userList, $user); // Add the user object to the array
      }
    }

    usort($userList, array("User", "cmp_obj"));

    return $userList;
  }
  
  /************************************************************************
	Delete the user
	************************************************************************/
  function deleteUser($id){
    if($this->enableSmfIntegration) {
      // Delete them from the steam id table first as it is a foreign key to gban_admins
      $this->db->sql_query("DELETE FROM gban_admin_steam
                            WHERE admin_id = '".$id."'");
      return true;
    } else {
      // Do not allow deletion of super users, especially if there is only 1 left
      $this->db->sql_query("SELECT count(*) as count FROM gban_admins
                            WHERE access_level = 1");

      $count = $this->db->get_row();
      $count = $count['count']; // Number of super-users

      // Get information about user about to be deleted
      $userObj = $this->getUserInfoById($id);

      // There needs to be at least 1 super user account for a delete to be allowed
      // As super users are the only ones allowed to delete
      if($count > 0) {
        // However, if there is only 1 super-user, they should not be allowed to
        // be deleted, all other users can be though
        if($count == 1 && $userObj->getAccessLevel() == 1) {
          return false; // Trying to delete last super-user (bad)
        } else {
          // Delete them from the steam id table first as it is a foreign key to gban_admins
          $this->db->sql_query("DELETE FROM gban_admin_steam
                                WHERE admin_id = '".$id."'");

          // The user can be deleted once their steam id has been removed
          $this->db->sql_query("DELETE FROM gban_admins
                                WHERE admin_id = '".$id."'");

          return true;
        }
      } else {
        return false;
      }
    }
  }
  
  /************************************************************************
	If the user forgets their current password, randomly generate a new password 
	and email it to them.
	This can be used by the user themselves or the super admin on the admin 
	management page for resetting passwords.
	************************************************************************/
  function forgotPassword($email) {    
    // make sure a valid email type of address was entered
		if(!preg_match("/^[A-Z0-9._%-]+@[A-Z0-9._%-]+\.[A-Z]{2,6}$/i", $email)) {
			return false;
		}
    
    // only do this if the email does exist
    if($this->emailExist($email)) {
      $pass = $this->createRandomPassword();
  
      // Update the database (email is unique)
      $this->db->sql_query("UPDATE gban_admins SET password = '".md5($pass)."'
                            WHERE email = '".$email."'");

      $this->db->sql_query("SELECT name FROM gban_admins WHERE email = '".$email."'");
      $username = $this->db->get_row();
      $username = $username['name'];
      
      // Send the email
      // To send HTML mail, the Content-type header must be set
      $headers  = "MIME-Version: 1.0" . "\r\n";
      $headers .= "Content-type: text/html; charset=utf-8" . "\r\n";			
      // Additional headers
      $headers .= "From: ".$this->siteName." Ban Management <".$this->emailFromHeader.">" . "\r\n";
      
      $subject = $this->siteName." Ban Management Password Reset";
      
      $body = "<html><body>";
      $body .= "<h2>".$this->siteName." Ban Management Password Reset</h2>";
      $body .= "<p>Your username is ".$username."<br/>Your new password is ".$pass."</p>";
      $body .= "<p>Please update your profile once logged in with a new password of your choice.</p>";
      $body .= "</body></html>";
      
      mail($email, $subject, $body, $headers);
      
      return true;
    }
    return false;
  }
  
  /************************************************************************
	Generate a random password
	************************************************************************/
  function createRandomPassword() {
    $chars = "abcdefghijkmnopqrstuvwxyz023456789";
    srand((double)microtime()*1000000);
    $i = 0;
    $pass = ''; // This is the new password

    while ($i <= 7) {
        $num = rand() % 33;
        $tmp = substr($chars, $num, 1);
        $pass = $pass . $tmp;
        $i++;
    }
    
    return $pass;
  }
  
  /************************************************************************
	Get the accessLevel based on the groupId.  This is only used for SMF
	integration.
	************************************************************************/
  function getAccessLevel($groupId, $otherGroups) {
    if($this->enableSmfIntegration) {
      $otherGroups = split(',', $otherGroups);
      if($groupId == $this->fullPowerGroup || in_array($this->fullPowerGroup, $otherGroups)) {
        return 1;
      } else if($groupId == $this->banManagerGroup || in_array($this->banManagerGroup, $otherGroups)) {
        return 2;
      } else if($groupId == $this->adminGroup || in_array($this->adminGroup, $otherGroups)) {
        return 3;
      } else if($groupId == $this->memberGroup || in_array($this->memberGroup, $otherGroups)) {
        return 4;
      } else  {
        return -1; // Invalid group found
      }
    } else {
      return $groupId;
    }
  }
}
?>
